﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class BanDAO
    {
        //ket noi lay danh sach ban
        public List<BanDTO> dsban()
        {
            string sql = "select * from Ban";
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<BanDTO> kq = new List<BanDTO>();
            SqlCommand ds = new SqlCommand(sql, conn);
            SqlDataReader read = ds.ExecuteReader();
            try
            {
                while (read.Read())
                {
                    BanDTO lma = new BanDTO();
                    lma.MaBan = read.GetString(0);
                    lma.TrangThai = read.GetString(1);
                    lma.ThoiGianDat = read.GetString(2);
                    lma.KhuVuc = read.GetString(3);
                    kq.Add(lma);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
            conn.Close();
            return kq;

        }


        public List<BanDTO> laydanhsachbantheotrangthai(string trangthai)
        {
            string sql = "select * from Ban where TrangThai =N'" + trangthai + "'";
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<BanDTO> KQ = new List<BanDTO>();
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                BanDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new BanDTO();
                    cn.MaBan = dtrow["MaBan"].ToString();
                    cn.ThoiGianDat = dtrow["ThoiGianDat"].ToString();
                    cn.KhuVuc = dtrow["KhuVuc"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        public List<BanDTO> LayBanTheoTrongNgayChuaThanhToan()
        {
            string sql = "select distinct Ban.MaBan from Ban, ChiTietHoaDon, HoaDon where Ban.MaBan = ChiTietHoaDon.MaBan and ChiTietHoaDon.MaHD = HoaDon.MAHD and HoaDon.TinhTrangHD like N'C%' and Ban.TrangThai = N'Bận' and CONVERT(varchar,HoaDon.GioVao,101)='" + DateTime.Parse(DateTime.Now.ToString()).ToString("MM/dd/yyyy") + "'";
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<BanDTO> KQ = new List<BanDTO>();
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                BanDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new BanDTO();
                    cn.MaBan = dtrow["MaBan"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        public List<BanDTO> LayDSBanTheoThoiGianDat(string timetu,string timeden)
        {
            string sql ="select * from Ban WHERE convert(char(19),ThoiGianDat, 120) BETWEEN '"+ timetu +"' AND '"+ timeden+"'";
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<BanDTO> KQ = new List<BanDTO>();
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                BanDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new BanDTO();
                    cn.MaBan = dtrow["MaBan"].ToString();
                    cn.TrangThai=dtrow["TrangThai"].ToString();
                    cn.ThoiGianDat=dtrow["ThoiGianDat"].ToString();
                    cn.KhuVuc = dtrow["KhuVuc"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        public int ThemBan(BanDTO bn)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "INSERT INTO Ban VALUES('" + bn.MaBan + "', N'" + bn.TrangThai + "', '" + bn.ThoiGianDat + "', N'" + bn.KhuVuc + "')";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public int XoaBan(string MaBN)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Delete Ban where MaBan='" + MaBN +"'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = -1;
            try
            {
                kq = cmd.ExecuteNonQuery();
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public string LayMaBan(string khuvuc)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select top 1 MaBan from Ban where MaBan like'"+ khuvuc +"%' order by MaBan desc";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            string kq = "";
            try
            {
                kq = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                conn.Close();
                return "";
            }
            conn.Close();
            return kq;
        }
        public string TaoMa(string MaCuoi, string KhuVuc)
        {
            string kq = KhuVuc;
            if (MaCuoi == "")
            {
                kq += "000";
            }
            else
            {
                int ma = int.Parse(MaCuoi.Substring(2, 3)) + 1;
                for (int i = 3; i > ma.ToString().Length; i--)
                {
                    kq += "0";
                }
                kq += ma;
            }         
            return kq;
        }
        public int KiemTraBan(string MaMN)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select count(MaBan) from ChiTietHoaDon where MaBan='" + MaMN + "'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = -1;
            try
            {
                kq = int.Parse(cmd.ExecuteScalar().ToString());
            }
            catch
            {
                conn.Close();
                return 0;
            }
            conn.Close();
            if (kq > 0)
                return 1;
            return 0;
        }
        public List<BanDTO> LayDanhSachBan()
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<BanDTO> KQ = new List<BanDTO>();
            string sql = "select * from Ban";
            SqlDataAdapter dtadp = new SqlDataAdapter(sql, conn);
            try
            {
                DataTable dt = new DataTable();
                dtadp.Fill(dt);
                BanDTO cn;
                foreach (DataRow dtrow in dt.Rows)
                {
                    cn = new BanDTO();
                    cn.MaBan = dtrow["MaBan"].ToString();
                    cn.TrangThai = dtrow["TrangThai"].ToString();
                    cn.ThoiGianDat = dtrow["ThoiGianDat"].ToString();
                    cn.KhuVuc = dtrow["KhuVuc"].ToString();
                    KQ.Add(cn);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
            conn.Close();
            return KQ;
        }
        public int CapNhatBan(BanDTO mn)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "Update Ban set TrangThai=N'" + mn.TrangThai + "' where MaBan='" + mn.MaBan+"'";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq >= 0)
                return 1;
            else
                return 0;
        }
        public string LayMaBanTuMaHD(int MaHD)
        {
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            string sql = "select top 1 maban from ChiTietHoaDon where mahd = " + MaHD;
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandText = sql;
            string kq = "";
            try
            {
                kq = cmd.ExecuteScalar().ToString();
            }
            catch
            {
                conn.Close();
                return "";
            }
            conn.Close();
            return kq;
        }


        public List<BanDTO> dsban_new()
        {
            string sql = "select * from Ban";
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<BanDTO> kq = new List<BanDTO>();

            SqlCommand ds = new SqlCommand(sql, conn);
            SqlDataReader read = ds.ExecuteReader();
            try
            {
                while (read.Read())
                {
                    BanDTO lma = new BanDTO();
                    lma.MaBan = read.GetString(0);
                    lma.TrangThai = read.GetString(1);
                    lma.ThoiGianDat = read.GetDateTime(2).ToString();//đáng ra chỗ này hình như là getdatetime ham cua thang kết ấy để xem lại cái
                    lma.KhuVuc = read.GetString(3);//copy nguyên chóc cái hàm này ra thành
                    kq.Add(lma);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
            conn.Close();
            return kq;

        }
        public List<BanDTO> dsban_khuvuc()
        {
            string sql = "select distinct khuvuc from Ban";
            SqlConnection conn = DataProvider.sqlconn;
            conn.Open();
            List<BanDTO> kq = new List<BanDTO>();

            SqlCommand ds = new SqlCommand(sql, conn);
            SqlDataReader read = ds.ExecuteReader();
            try
            {
                while (read.Read())
                {
                    BanDTO lma = new BanDTO();
                   // //lma.MaBan = read.GetString(0);
                   // lma.TrangThai = read.GetString(1);
                   // lma.ThoiGianDat = read.GetDateTime(2).ToString();//đáng ra chỗ này hình như là getdatetime ham cua thang kết ấy để xem lại cái
                    lma.KhuVuc = read.GetString(0);//copy nguyên chóc cái hàm này ra thành 0 mà
                    kq.Add(lma);
                }
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
            conn.Close();
            return kq;

        }




    }
}
